﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// 文件名：ClsSellManageDAL.cs
/// 作  者：黄纲
/// 时  间：2006.11.26
/// 说  明：ClsSellManageDAL类用于销售管理信息相关处理
/// </summary>
public class ClsSellManageDAL
{
    #region  数据库连接字符串
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    /// <summary>
    /// 终止单据
    /// </summary>
    /// <param name="No">单据编号</param>
    /// <param name="TableName">数据库表名</param>
    /// <param name="TableName">主键列名</param>
    /// <returns></returns>
    public Boolean EndBillOfDocument(string No, string TableName, string Columnname)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_EndBillOfDocument", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 30);
        com.Parameters["@no"].Value = No;
        com.Parameters.Add("@tabname", SqlDbType.NVarChar, 60);
        com.Parameters["@tabname"].Value = TableName;
        com.Parameters.Add("@colname", SqlDbType.NVarChar, 60);
        com.Parameters["@colname"].Value = Columnname;

        try
        {
            con.Open();

            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    #endregion

    #region 销售合同管理

    /// <summary>
    /// 获取销售合同主表信息
    /// </summary>
    /// <param name="Param">查询参数</param>
    /// <param name="UserID">当前登陆用户ID</param>
    /// <param name="QueryMode">查询方式(0为精确，1为模糊)</param>
    /// <param name="Type">返回数据集类型(0全部合同,1确认过的合同,2未确认的合同,3已发货完的合同,4未发货完的合同)</param>
    /// <returns>返回数据集</returns>
    public DataSet GetSellContractDataSet(string Param, string UserID, int QueryMode, int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSellContractDataSet", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@param", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@param"].Value = Param;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;
        dat.SelectCommand.Parameters.Add("@mode", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@mode"].Value = QueryMode;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 新增或修改销售合同主表记录
    /// </summary>
    /// <param name="Cm_No">销售订单号</param>
    /// <param name="Cm_Date">订单日期</param>
    /// <param name="Cm_Mdate">希望到货期</param>
    /// <param name="Ct_Id">客户</param>
    /// <param name="Accounts">付款单位帐号</param>
    /// <param name="Linkman">联系人</param>
    /// <param name="Tel">联系电话</param>
    /// <param name="Fax">传真</param>
    /// <param name="Email">电子邮箱</param>
    /// <param name="Cm_Addr">收货地址</param>
    /// <param name="Postcode">邮编</param>
    /// <param name="Sm_Id">运输方式</param>
    /// <param name="Account_Date">帐期</param>
    /// <param name="Dp_Id">部门</param>
    /// <param name="Wk_Id">业务员</param>
    /// <param name="Oper">录入人</param>
    /// <param name="Remark">备注</param>
    /// <param name="OperateType">操作类型(0新增，1修改)</param>
    /// <returns>返回操作是否成功</returns>
    public Boolean AddOrUpdateSellContract(string Cm_No, DateTime Cm_Date, DateTime Cm_Mdate, string Ct_Id, string Accounts, string Linkman, string Tel, string Fax, string Email, string Cm_Addr, string Postcode, string Sm_Id, string Account_Date, string Dp_Id, string Wk_Id, string Oper, string Remark, int OperateType)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateSellContract", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@cm_date", SqlDbType.DateTime, 8);
        com.Parameters["@cm_date"].Value = Cm_Date;
        com.Parameters.Add("@cm_mdate", SqlDbType.DateTime, 8);
        com.Parameters["@cm_mdate"].Value = Cm_Mdate;
        com.Parameters.Add("@ct_id", SqlDbType.NVarChar, 20);
        com.Parameters["@ct_id"].Value = Ct_Id;
        com.Parameters.Add("@acc", SqlDbType.NVarChar, 60);
        com.Parameters["@acc"].Value = Accounts;
        com.Parameters.Add("@li_man", SqlDbType.NVarChar, 60);
        com.Parameters["@li_man"].Value = Linkman;
        com.Parameters.Add("@tel", SqlDbType.NVarChar, 60);
        com.Parameters["@tel"].Value = Tel;
        com.Parameters.Add("@fax", SqlDbType.NVarChar, 60);
        com.Parameters["@fax"].Value = Fax;
        com.Parameters.Add("@email", SqlDbType.NVarChar, 255);
        com.Parameters["@email"].Value = Email;
        com.Parameters.Add("@cm_add", SqlDbType.NVarChar, 255);
        com.Parameters["@cm_add"].Value = Cm_Addr;
        com.Parameters.Add("@pos", SqlDbType.NVarChar, 20);
        com.Parameters["@pos"].Value = Postcode;
        com.Parameters.Add("@sm_id", SqlDbType.NVarChar, 20);
        com.Parameters["@sm_id"].Value = Sm_Id;
        com.Parameters.Add("@acc_date", SqlDbType.NVarChar, 60);
        com.Parameters["@acc_date"].Value = Account_Date;
        com.Parameters.Add("@dp_id", SqlDbType.NVarChar, 20);
        com.Parameters["@dp_id"].Value = Dp_Id;
        com.Parameters.Add("@wk_id", SqlDbType.NVarChar, 20);
        com.Parameters["@wk_id"].Value = Wk_Id;
        com.Parameters.Add("@oper", SqlDbType.NVarChar, 60);
        com.Parameters["@oper"].Value = Oper;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;
        com.Parameters.Add("@operate_type", SqlDbType.Int, 4);
        com.Parameters["@operate_type"].Value = OperateType;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 改变合同订单的状态
    /// </summary>
    /// <param name="Cm_No">合同单号</param>
    /// <param name="State">操作状态(0确认，1反审即终止，2作废即作废，3彻底作废，4设置为已发货完,5设置为未发货完)</param>
    /// <param name="Auditor">操作员</param>
    /// <returns>操作是否成功</returns>
    public Boolean UpdateSellContractState(string Cm_No, int State, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateSellContractState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@cm_state", SqlDbType.Int, 4);
        com.Parameters["@cm_state"].Value = State;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 获取所有销售合同从表信息
    /// <param name="Cm_No">合同单号</param>
    /// <param name="Type">数据集类型(0全部,1未发货完的,2发货完的)</param>
    /// </summary>
    /// <returns>返回数据集</returns>
    public DataSet GetSellContractDataSetChild(string Cm_No, int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSellContractDataSetChild", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@cm_no"].Value = Cm_No;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据商品编号获取商品原价
    /// </summary>
    /// <param name="Im_No">商品编号</param>
    /// <returns></returns>
    public decimal GetSellContractChildOldPrice(string Im_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_GetSellContractChildOldPrice", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;

        try
        {
            con.Open();
            return decimal.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 根据商品编号获取商品原折扣
    /// </summary>
    /// <param name="Cm_No">订单编号</param>
    /// <param name="Im_No">商品编号</param>
    /// <returns></returns>
    public decimal GetSellContractChildOldAgio(string Cm_No, string Im_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_GetSellContractChildOldAgio", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;

        try
        {
            con.Open();
            return decimal.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 新增或修改销售合同从表记录
    /// </summary>
    /// <param name="Index">记录ID号(新增时为0)</param>
    /// <param name="Cm_No">销售合同订单号</param>
    /// <param name="Im_No">商品编号</param>
    /// <param name="Lot_No">商品批号</param>
    /// <param name="Price">商品价格</param>
    /// <param name="Qty">数量</param>
    /// <param name="Qty1">未发货数量</param>
    /// <param name="Agio">折扣率</param>
    /// <param name="Remark">备注</param>
    /// <param name="OperateType">操作类型(0新增，1修改)</param>
    /// <returns>返回操作是否成功</returns>
    public Boolean AddOrUpdateSellContracChild(int Index, string Cm_No, string Im_No, string Lot_No, decimal Price, decimal Qty, decimal Qty1, decimal Agio, string Remark, int OperateType)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateSellContracChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@index", SqlDbType.Int, 4);
        com.Parameters["@index"].Value = Index;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 60);
        com.Parameters["@lot_no"].Value = Lot_No;
        com.Parameters.Add("@price", SqlDbType.Decimal, 28);
        com.Parameters["@price"].Value = Price;
        com.Parameters.Add("@qty", SqlDbType.Decimal, 28);
        com.Parameters["@qty"].Value = Qty;
        com.Parameters.Add("@qty1", SqlDbType.Decimal, 28);
        com.Parameters["@qty1"].Value = Qty1;
        com.Parameters.Add("@agio", SqlDbType.Decimal, 28);
        com.Parameters["@agio"].Value = Agio;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;
        com.Parameters.Add("@operate_type", SqlDbType.Int, 4);
        com.Parameters["@operate_type"].Value = OperateType;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断同一合同订单中是否存在同类商品
    /// </summary>
    /// <param name="Cm_No">合同订单号</param>
    /// <param name="Im_No">商品编号</param>
    /// <returns>返回存在数量</returns>
    public int SellContractChildExistSameMateriel(string Cm_No, string Im_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_SellContractChildExistSameMateriel", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;

        try
        {
            con.Open();
            return Int32.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 改变合同订单从表的状态
    /// </summary>
    /// <param name="Index">合同ID号</param>
    /// <param name="State">操作状态(0确认，1反审即终止，2作废即作废，3彻底作废)</param>
    /// <returns>操作是否成功</returns>
    public Boolean UpdateSellContractStateChild(int Index, int State)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateSellContractStateChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@index", SqlDbType.Int, 4);
        com.Parameters["@index"].Value = Index;
        com.Parameters.Add("@state", SqlDbType.Int, 4);
        com.Parameters["@state"].Value = State;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 获取未发货的商品数量
    /// </summary>
    /// <param name="Cm_No">订单号</param>
    /// <param name="Im_No">料号</param>
    /// <returns>返回未发货的商品数量</returns>
    public decimal GetSellContractChildRsSpareQty(string Cm_No, string Im_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_GetSellContractChildRsSpareQty", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;

        try
        {
            con.Open();
            return decimal.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return 0;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断合同订单存在状况
    /// </summary>
    /// <param name="Cm_No">合同订单号</param>
    /// <param name="Type">类型(1是否存在发货,2是否存在子单)</param>
    /// <returns>返回存在数量</returns>
    public int SellContractExistState(string Cm_No, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_SellContractExistState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@type", SqlDbType.Int, 4);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            return Int32.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    #endregion

    #region 销售发货管理

    /// <summary>
    /// 获取所有销售发货主表信息
    /// </summary>
    /// <param name="Dm_Type">类别(发货dm_type='1')</param>
    /// <param name="UserID">当前登陆用户ID</param>
    /// <param name="Type">返回数据集类型(0全部发货记录,1确认过的发货记录,2未确认的发货记录)</param>
    /// <returns>返回数据集</returns>
    public DataSet GetSellConsignmentDataSet(string Dm_Type, string Param, string UserID, int QueryMode, int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSellConsignmentDataSet", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@dm_type", SqlDbType.NVarChar, 2);
        dat.SelectCommand.Parameters["@dm_type"].Value = Dm_Type;
        dat.SelectCommand.Parameters.Add("@param", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@param"].Value = Param;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;
        dat.SelectCommand.Parameters.Add("@mode", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@mode"].Value = QueryMode;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 确认销售发货单
    /// </summary>
    /// <param name="Cm_No">合同订单号</param>
    /// <param name="Dm_No">发货单号</param>
    /// <param name="Auditor">操作员</param>
    /// <returns></returns>
    public int AuditSellConsignmentState(string Cm_No, string Dm_No, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditSellConsignmentState", con);
        SqlParameter PrmOutPut = new SqlParameter();

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@dm_no"].Value = Dm_No;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;
        PrmOutPut = com.Parameters.Add("@errcount", SqlDbType.Int, 4);
        PrmOutPut.Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return Int32.Parse(com.Parameters["@errcount"].Value.ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 取消确认销售发货单
    /// </summary>
    /// <param name="Cm_No">合同订单号</param>
    /// <param name="Dm_No">发货单号</param>
    /// <param name="Type">(0作废，1彻底作废，-1取消确认)</param>
    /// <returns></returns>
    public Boolean OAuditSellConsignmentState(string Cm_No, string Dm_No, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_OAuditSellConsignmentState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@dm_no"].Value = Dm_No;
        com.Parameters.Add("@type", SqlDbType.Int, 4);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 新增或修改销售发货主表记录
    /// </summary>
    /// <param name="Dm_Type">类别(发货dm_type='1')</param>
    /// <param name="Dm_No">发货单号</param>
    /// <param name="Dm_Date">发货日期</param>
    /// <param name="Cm_No">销售订单号</param>
    /// <param name="Cm_Date">订单日期</param>
    /// <param name="Cm_Mdate">希望到货期</param>
    /// <param name="Ct_Id">客户</param>
    /// <param name="Accounts">付款单位帐号</param>
    /// <param name="Linkman">联系人</param>
    /// <param name="Tel">联系电话</param>
    /// <param name="Fax">传真</param>
    /// <param name="Email">电子邮箱</param>
    /// <param name="Cm_Addr">收货地址</param>
    /// <param name="Postcode">邮编</param>
    /// <param name="Sm_Id">运输方式</param>
    /// <param name="Account_Date">帐期</param>
    /// <param name="Dp_Id">部门</param>
    /// <param name="Wk_Id">业务员</param>
    /// <param name="Oper">录入人</param>
    /// <param name="Remark">备注</param>
    /// <param name="OperateType">操作类型(0新增，1修改)</param>
    /// <returns>返回操作是否成功</returns>
    public Boolean AddOrUpdateSellConsignment(string Dm_Type, string Dm_No, DateTime Dm_Date, string Cm_No, string Cm_Date, DateTime Cm_Mdate, string Ct_Id, string Accounts, string Linkman, string Tel, string Fax, string Email, string Cm_Addr, string Postcode, string Sm_Id, string Account_Date, string Dp_Id, string Wk_Id, string Oper, string Remark, int OperateType)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateSellConsignment", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@dm_type", SqlDbType.NVarChar, 2);
        com.Parameters["@dm_type"].Value = Dm_Type;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@dm_no"].Value = Dm_No;
        com.Parameters.Add("@dm_date", SqlDbType.DateTime, 8);
        com.Parameters["@dm_date"].Value = Dm_Date;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@cm_date", SqlDbType.NVarChar, 20);
        com.Parameters["@cm_date"].Value = Cm_Date;
        com.Parameters.Add("@cm_mdate", SqlDbType.DateTime, 8);
        com.Parameters["@cm_mdate"].Value = Cm_Mdate;
        com.Parameters.Add("@ct_id", SqlDbType.NVarChar, 20);
        com.Parameters["@ct_id"].Value = Ct_Id;
        com.Parameters.Add("@acc", SqlDbType.NVarChar, 60);
        com.Parameters["@acc"].Value = Accounts;
        com.Parameters.Add("@li_man", SqlDbType.NVarChar, 60);
        com.Parameters["@li_man"].Value = Linkman;
        com.Parameters.Add("@tel", SqlDbType.NVarChar, 30);
        com.Parameters["@tel"].Value = Tel;
        com.Parameters.Add("@fax", SqlDbType.NVarChar, 20);
        com.Parameters["@fax"].Value = Fax;
        com.Parameters.Add("@email", SqlDbType.NVarChar, 60);
        com.Parameters["@email"].Value = Email;
        com.Parameters.Add("@cm_add", SqlDbType.NVarChar, 255);
        com.Parameters["@cm_add"].Value = Cm_Addr;
        com.Parameters.Add("@pos", SqlDbType.NVarChar, 20);
        com.Parameters["@pos"].Value = Postcode;
        com.Parameters.Add("@sm_id", SqlDbType.NVarChar, 20);
        com.Parameters["@sm_id"].Value = Sm_Id;
        com.Parameters.Add("@acc_date", SqlDbType.NVarChar, 30);
        com.Parameters["@acc_date"].Value = Account_Date;
        com.Parameters.Add("@dp_id", SqlDbType.NVarChar, 20);
        com.Parameters["@dp_id"].Value = Dp_Id;
        com.Parameters.Add("@wk_id", SqlDbType.NVarChar, 20);
        com.Parameters["@wk_id"].Value = Wk_Id;
        com.Parameters.Add("@oper", SqlDbType.NVarChar, 60);
        com.Parameters["@oper"].Value = Oper;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;
        com.Parameters.Add("@operate_type", SqlDbType.Int, 4);
        com.Parameters["@operate_type"].Value = OperateType;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 获取所有销售发货从表信息
    /// </summary>
    /// <param name="Dd_Type">类别(发货dd_type='1')</param>
    /// <param name="Dm_No">出货单号</param>
    /// <returns>返回数据集</returns>
    public DataSet GetSellConsignmentDataSetChild(string Dd_Type, string Dm_No)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSellConsignmentDataSetChild", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@dd_type", SqlDbType.NVarChar, 2);
        dat.SelectCommand.Parameters["@dd_type"].Value = Dd_Type;
        dat.SelectCommand.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@dm_no"].Value = Dm_No;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 改变销售发货从表的状态
    /// </summary>
    /// <param name="Dd_Type">类别(发货dd_type='1')</param>
    /// <param name="Dm_No">出货单号</param>
    /// <param name="Im_No">料号</param>
    /// <param name="State">操作状态(0确认，1反审即终止，2作废即作废，3彻底作废)</param>
    /// <returns>操作是否成功</returns>
    public Boolean UpdateSellConsignmentStateChild(string Dd_Type, string Dm_No, string Im_No, int State)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateSellConsignmentStateChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@dd_type", SqlDbType.NVarChar, 2);
        com.Parameters["@dd_type"].Value = Dd_Type;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@dm_no"].Value = Dm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;
        com.Parameters.Add("@state", SqlDbType.Int, 4);
        com.Parameters["@state"].Value = State;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改销售发货从表信息
    /// </summary>
    /// <param name="Dd_Type">类别(发货dd_type='1')</param>
    /// <param name="Dm_No">出货单号</param>
    /// <param name="Im_No">料号</param>
    /// <param name="Qty">未发货数量</param>
    /// <param name="Remark">备注</param>
    /// <returns>操作是否成功</returns>
    public Boolean UpdateSellConsignmentInfoChild(string Dd_Type, string Dm_No, string Im_No, string Lot_No, decimal Qty, string Remark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateSellConsignmentInfoChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@dd_type", SqlDbType.NVarChar, 2);
        com.Parameters["@dd_type"].Value = Dd_Type;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@dm_no"].Value = Dm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);
        com.Parameters["@lot_no"].Value = Lot_No;
        com.Parameters.Add("@qty", SqlDbType.Decimal, 28);
        com.Parameters["@qty"].Value = Qty;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;


        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 获取未确认的商品数量
    /// </summary>
    /// <param name="Dm_No">发货单号</param>
    /// <param name="Im_No">料号</param>
    /// <returns>返回未确认的商品数量</returns>
    public decimal GetSellConsignmentChildRsSpareQty(string Dm_No, string Im_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_GetSellConsignmentChildRsSpareQty", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@dm_no"].Value = Dm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;

        try
        {
            con.Open();
            return decimal.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return 0;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断销售发货是否存在发货确认
    /// </summary>
    /// <param name="M_No"></param>
    /// <returns></returns>
    public int SellConsignmentExistState(string M_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_SellConsignmentExistState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@s_up_no", SqlDbType.NVarChar, 50);
        com.Parameters["@s_up_no"].Value = M_No;

        try
        {
            con.Open();
            return Int32.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    #endregion

    #region 出库确认管理

    /// <summary>
    /// 确认出库确认主单
    /// </summary>
    /// <param name="S_Type">类型</param>
    /// <param name="M_No">发货单号</param>
    /// <param name="N_No">出库确认单号</param>
    /// <param name="Auditor">确认人</param>
    /// <returns></returns>
    public int AuditShipmentAffirm(int S_Type, string M_No, string N_No, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditShipmentAffirm", con);
        SqlParameter PrmOutPut = new SqlParameter();

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@s_type", SqlDbType.Int, 4);
        com.Parameters["@s_type"].Value = S_Type;
        com.Parameters.Add("@m_no", SqlDbType.NVarChar, 50);
        com.Parameters["@m_no"].Value = M_No;
        com.Parameters.Add("@n_no", SqlDbType.NVarChar, 50);
        com.Parameters["@n_no"].Value = N_No;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 50);
        com.Parameters["@auditor"].Value = Auditor;
        PrmOutPut = com.Parameters.Add("@errcount", SqlDbType.Int, 4);
        PrmOutPut.Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return Int32.Parse(com.Parameters["@errcount"].Value.ToString());            
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 取消确认出库确认主单
    /// </summary>
    /// <param name="S_Type">类型</param>
    /// <param name="M_No">出库单号</param>
    /// <param name="N_No">出库确认单号</param>
    /// <returns></returns>
    public Boolean OAuditShipmentAffirm(int S_Type, string M_No, string N_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_OAuditShipmentAffirm", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@s_type", SqlDbType.Int, 4);
        com.Parameters["@s_type"].Value = S_Type;
        com.Parameters.Add("@m_no", SqlDbType.NVarChar, 50);
        com.Parameters["@m_no"].Value = M_No;
        com.Parameters.Add("@n_no", SqlDbType.NVarChar, 50);
        com.Parameters["@n_no"].Value = N_No;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断此单是否已有开票申请记录
    /// </summary>
    /// <param name="M_No"></param>
    /// <param name="Type"></param>
    /// <returns></returns>
    public int ShipmentAffirmExistState(string No, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_ShipmentAffirmExistState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@up_no", SqlDbType.NVarChar, 50);
        com.Parameters["@up_no"].Value = No;
        com.Parameters.Add("@type", SqlDbType.Int, 4);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            return Int32.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    #endregion

    #region 销售退货管理

    /// <summary>
    /// 获取销售退货主单信息
    /// </summary>
    /// <param name="Param">查询参数</param>
    /// <param name="UserID">当前登陆用户ID</param>
    /// <param name="QueryMode">查询方式(0为精确，1为模糊)</param>
    /// <param name="Type">返回数据集类型(0全部退货,1确认过的退货,2未确认的退货,3已终止,4未终止,5已作废,6未作废)</param>
    /// <returns>返回数据集</returns>
    public DataSet GetSellBackDataSet(string Param, string UserID, int QueryMode, int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSellBackDataSet", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@param", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@param"].Value = Param;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;
        dat.SelectCommand.Parameters.Add("@mode", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@mode"].Value = QueryMode;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 新增或修改销售退货主单信息
    /// </summary>
    /// <param name="No"></param>
    /// <param name="BackDate"></param>
    /// <param name="CM_No"></param>
    /// <param name="Man"></param>
    /// <param name="Tel"></param>
    /// <param name="Addr"></param>
    /// <param name="Zip"></param>
    /// <param name="Dp_No"></param>
    /// <param name="Wk_No"></param>
    /// <param name="Remark"></param>
    /// <param name="Oper"></param>
    /// <returns></returns>
    public Boolean AddOrUpdateSellBackInfo(string No, DateTime BackDate, string CM_No, string Man, string Tel, string Addr, string Zip, string Dp_No, string Wk_No, string Remark, string Oper)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateSellBackInfo", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 50);
        com.Parameters["@no"].Value = No;
        com.Parameters.Add("@back_date", SqlDbType.DateTime, 8);
        com.Parameters["@back_date"].Value = BackDate;
        com.Parameters.Add("@c_no", SqlDbType.NVarChar, 50);
        com.Parameters["@c_no"].Value = CM_No;
        com.Parameters.Add("@man", SqlDbType.NVarChar, 50);
        com.Parameters["@man"].Value = Man;
        com.Parameters.Add("@tel", SqlDbType.NVarChar, 50);
        com.Parameters["@tel"].Value = Tel;
        com.Parameters.Add("@addr", SqlDbType.NVarChar, 255);
        com.Parameters["@addr"].Value = Addr;
        com.Parameters.Add("@zip", SqlDbType.NVarChar, 10);
        com.Parameters["@zip"].Value = Zip;
        com.Parameters.Add("@dp_id", SqlDbType.NVarChar, 50);
        com.Parameters["@dp_id"].Value = Dp_No;
        com.Parameters.Add("@wk_id", SqlDbType.NVarChar, 50);
        com.Parameters["@wk_id"].Value = Wk_No;
        com.Parameters.Add("@oper", SqlDbType.NVarChar, 50);
        com.Parameters["@oper"].Value = Oper;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 新增或修改销售退货从单信息
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="No"></param>
    /// <param name="Im_No"></param>
    /// <param name="P_No"></param>
    /// <param name="Qty"></param>
    /// <param name="Price"></param>
    /// <param name="Remark"></param>
    /// <returns></returns>
    public Boolean AddOrUpdateSellBackChild(int ID, string No, string Im_No, string P_No, decimal Qty, decimal Price, string Remark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateSellBackChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 50);
        com.Parameters["@no"].Value = No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 50);
        com.Parameters["@im_no"].Value = Im_No;
        com.Parameters.Add("@p_no", SqlDbType.NVarChar, 50);
        com.Parameters["@p_no"].Value = P_No;
        com.Parameters.Add("@qty", SqlDbType.Decimal, 28);
        com.Parameters["@qty"].Value = Qty;
        com.Parameters.Add("@price", SqlDbType.Decimal, 28);
        com.Parameters["@price"].Value = Price;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 改变销售退货主单状态
    /// </summary>
    /// <param name="ID">退货主单ID</param>
    /// <param name="State">0确认，1反审即终止，2作废即作废，3彻底作废</param>
    /// <param name="Auditor">操作员</param>
    /// <returns></returns>
    public Boolean UpdateSellBackState(int ID, int State, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateSellBackState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@cm_state", SqlDbType.Int, 4);
        com.Parameters["@cm_state"].Value = State;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 获取所有销售退货从表信息
    /// <param name="M_No">退货单号</param>
    /// <param name="Type">数据集类型(0全部,1未确认完的,2确认完的)</param>
    /// </summary>
    /// <returns>返回数据集</returns>
    public DataSet GetSellBackDataSetChild(string M_No, int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSellBackDataSetChild", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@cm_no"].Value = M_No;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 作废销售退货从单
    /// </summary>
    /// <param name="ID">销售退货从单ID</param>
    /// <param name="Type">0作废，1彻底作废</param>
    /// <returns></returns>
    public Boolean DeleteSellBackChild(int ID, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteSellBackChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@type", SqlDbType.Int, 4);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 获取未确认的商品数量
    /// </summary>
    /// <param name="M_No">退货单号</param>
    /// <param name="Im_No">料号</param>
    /// <returns>返回未确认的商品数量</returns>
    public decimal GetSellBacktChildRsSpareQty(string M_No, string Im_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_GetSellBacktChildRsSpareQty", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@s_no", SqlDbType.NVarChar, 50);
        com.Parameters["@s_no"].Value = M_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 50);
        com.Parameters["@im_no"].Value = Im_No;

        try
        {
            con.Open();
            return decimal.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return 0;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断同一销售退货单中是否存在同类商品
    /// </summary>
    /// <param name="M_No"></param>
    /// <param name="Im_No"></param>
    /// <returns></returns>
    public int SellBackChildExistSameMateriel(string M_No, string Im_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_SellBackChildExistSameMateriel", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 50);
        com.Parameters["@cm_no"].Value = M_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 50);
        com.Parameters["@im_no"].Value = Im_No;

        try
        {
            con.Open();
            return Int32.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断销售退货是否存在退货确认
    /// </summary>
    /// <param name="S_type"></param>
    /// <param name="M_No"></param>
    /// <returns></returns>
    public int SellBackExistState(int S_type, string M_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_SellBackExistState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@s_type", SqlDbType.Int, 4);
        com.Parameters["@s_type"].Value = S_type;
        com.Parameters.Add("@s_up_no", SqlDbType.NVarChar, 50);
        com.Parameters["@s_up_no"].Value = M_No;

        try
        {
            con.Open();
            return Int32.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    #endregion

    #region 退货确认管理

    /// <summary>
    /// 获取销售退货确认主单信息
    /// </summary>
    /// <param name="S_Type"></param>
    /// <param name="Param">查询参数</param>
    /// <param name="UserID">当前登陆用户ID</param>
    /// <param name="QueryMode">查询方式(0为精确，1为模糊)</param>
    /// <param name="Type">返回数据集类型(0全部,1确认过的,2未确认的)</param>
    /// <returns>返回数据集</returns>
    public DataSet GetSellAffirmDataSet(int S_Type, string Param, string UserID, int QueryMode, int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSellAffirmDataSet", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@s_type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@s_type"].Value = S_Type;
        dat.SelectCommand.Parameters.Add("@param", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@param"].Value = Param;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;
        dat.SelectCommand.Parameters.Add("@mode", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@mode"].Value = QueryMode;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取当前客户的退货信息信息
    /// </summary>
    /// <param name="Param">查询参数</param>
    /// <param name="Mode">查询方式(0为精确,1为模糊)</param>
    /// <param name="CurrCustomer">客户编号</param>
    /// <param name="CurrUser">当前登陆用户ID</param>
    /// <returns></returns>
    public DataSet GetSellAffirmDataSet(string Param, int Mode, string CurrCustomer, string CurrUser)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetCurrCustomerSellBackDataSet", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@param", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@param"].Value = Param;
        dat.SelectCommand.Parameters.Add("@mode", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@mode"].Value = Mode;
        dat.SelectCommand.Parameters.Add("@cus_no", SqlDbType.NVarChar, 50);
        dat.SelectCommand.Parameters["@cus_no"].Value = CurrCustomer;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = CurrUser;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取所有销售退货确认从表信息
    /// <param name="S_No">退货确认单号</param>
    /// <param name="S_Type">数据集类型(0全部,1未确认完的,2确认完的)</param>
    /// </summary>
    /// <returns>返回数据集</returns>
    public DataSet GetSellAffirmDataSetChild(int S_Type, string S_No, int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSellAffirmDataSetChild", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@s_type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@s_type"].Value = S_Type;
        dat.SelectCommand.Parameters.Add("@s_no", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@s_no"].Value = S_No;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 新增或修改销售退货确认主单信息
    /// </summary>
    /// <param name="No_Type"></param>
    /// <param name="S_No"></param>
    /// <param name="S_Date"></param>
    /// <param name="Up_No"></param>
    /// <param name="Wh_No"></param>
    /// <param name="Remark"></param>
    /// <param name="Oper"></param>
    /// <returns></returns>
    public Boolean AddOrUpdateSellAffirmInfo(int No_Type, string S_No, DateTime S_Date, string Up_No, string Wh_No, string Remark, string Oper)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateSellAffirmInfo", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@s_type", SqlDbType.Int, 4);
        com.Parameters["@s_type"].Value = No_Type;
        com.Parameters.Add("@s_no", SqlDbType.NVarChar, 50);
        com.Parameters["@s_no"].Value = S_No;
        com.Parameters.Add("@s_date", SqlDbType.DateTime, 8);
        com.Parameters["@s_date"].Value = S_Date;
        com.Parameters.Add("@s_up_no", SqlDbType.NVarChar, 50);
        com.Parameters["@s_up_no"].Value = Up_No;
        com.Parameters.Add("@wh_no", SqlDbType.NVarChar, 50);
        com.Parameters["@wh_no"].Value = Wh_No;
        com.Parameters.Add("@oper", SqlDbType.NVarChar, 50);
        com.Parameters["@oper"].Value = Oper;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改销售退货确认从单信息
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="P_No"></param>
    /// <param name="Price"></param>
    /// <param name="Qty"></param>
    /// <param name="Remark"></param>
    /// <returns></returns>
    public Boolean UpdateSellAffirmChild(int ID, string P_No, decimal Price, decimal Qty, string Remark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateSellAffirmChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@p_no", SqlDbType.NVarChar, 50);
        com.Parameters["@p_no"].Value = P_No;
        com.Parameters.Add("@price", SqlDbType.Decimal, 28);
        com.Parameters["@price"].Value = Price;
        com.Parameters.Add("@qty", SqlDbType.Decimal, 28);
        com.Parameters["@qty"].Value = Qty;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废退货确认信息
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="State"></param>
    /// <param name="Auditor"></param>
    /// <returns></returns>
    public Boolean DeleteSellAffirmInfo(int ID, int State)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteSellAffirmInfo", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@state", SqlDbType.Int, 4);
        com.Parameters["@state"].Value = State;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废退货确认从表信息
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="Type"></param>
    /// <returns></returns>
    public Boolean DeleteSellAffirmChild(int ID, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteSellAffirmChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@state", SqlDbType.Int, 4);
        com.Parameters["@state"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 确认退货确认主单
    /// </summary>
    /// <param name="S_Type">类型</param>
    /// <param name="M_No">退货单号</param>
    /// <param name="N_No">退货确认单号</param>
    /// <param name="Auditor">确认人</param>
    /// <returns></returns>
    public int AuditSellAffirm(int S_Type, string M_No, string N_No, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditSellAffirm", con);
        SqlParameter PrmOutPut = new SqlParameter();

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@s_type", SqlDbType.Int, 4);
        com.Parameters["@s_type"].Value = S_Type;
        com.Parameters.Add("@m_no", SqlDbType.NVarChar, 50);
        com.Parameters["@m_no"].Value = M_No;
        com.Parameters.Add("@n_no", SqlDbType.NVarChar, 50);
        com.Parameters["@n_no"].Value = N_No;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 50);
        com.Parameters["@auditor"].Value = Auditor;
        PrmOutPut = com.Parameters.Add("@errcount", SqlDbType.Int, 4);
        PrmOutPut.Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return Int32.Parse(com.Parameters["@errcount"].Value.ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 取消确认退货确认主单
    /// </summary>
    /// <param name="S_Type">类型</param>
    /// <param name="M_No">退货单号</param>
    /// <param name="N_No">退货确认单号</param>
    /// <returns></returns>
    public Boolean OAuditSellAffirm(int S_Type, string M_No, string N_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_OAuditSellAffirm", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@s_type", SqlDbType.Int, 4);
        com.Parameters["@s_type"].Value = S_Type;
        com.Parameters.Add("@m_no", SqlDbType.NVarChar, 50);
        com.Parameters["@m_no"].Value = M_No;
        com.Parameters.Add("@n_no", SqlDbType.NVarChar, 50);
        com.Parameters["@n_no"].Value = N_No;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断销售退货单是否存在于退款
    /// </summary>
    /// <param name="No"></param>
    /// <param name="Type"></param>
    /// <returns></returns>
    public int SellAffirmExistState(string No, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_SellAffirmExistState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@up_no", SqlDbType.NVarChar, 50);
        com.Parameters["@up_no"].Value = No;
        com.Parameters.Add("@type", SqlDbType.Int, 4);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            return Int32.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    #endregion

    #region 销售退货/出库确认改变库存BY KIDY
    /// <summary>
    /// 销售退货/出库确认改变库存
    /// <returns>CREATE BY KIDY ON 20070124</returns>
    /// </summary>
    public String CheckWhetherAudit(string sS_No, string sTm_Type, int iState, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditSellBackOrOut", con);
        com.CommandType = CommandType.StoredProcedure;
        SqlParameter ParOutPut = new SqlParameter();

        com.Parameters.Add("@s_no", SqlDbType.NVarChar, 30);
        com.Parameters["@s_no"].Value = sS_No;
        com.Parameters.Add("@dm_state", SqlDbType.Int, 4);
        com.Parameters["@dm_state"].Value = iState;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;
        //com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        //com.Parameters["@type"].Value = sTm_Type;
        ParOutPut = com.Parameters.Add("@reIm_no", SqlDbType.VarChar, 30);
        ParOutPut.Direction = ParameterDirection.Output;
        ParOutPut = com.Parameters.Add("@relot_no", SqlDbType.NVarChar, 30);
        ParOutPut.Direction = ParameterDirection.Output;
        ParOutPut = com.Parameters.Add("@returnState", SqlDbType.NVarChar, 2);
        ParOutPut.Direction = ParameterDirection.Output;
        try
        {
            con.Open();
            com.ExecuteNonQuery();
            string strState = com.Parameters["@returnState"].Value.ToString();//返回状态（N:未入库;Q:库存不足）
            string strReIm_no = com.Parameters["@reIm_no"].Value.ToString();//返回的商品号
            string strReLot_no = com.Parameters["@relot_no"].Value.ToString();//返回的批号
            if (strState == "N")
            {
                return "N" + "|" + strReIm_no + "|" + strReLot_no;
            }
            else if (strState == "Q")
            {
                return "Q" + "|" + strReIm_no + "|" + strReLot_no;
            }
            else
            {
                return "0";
            }
        }
        catch
        {
            return "";
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }
    #endregion
}
